package jsyf.oa.dao.system;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.system.LogsEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.*;

@SuppressWarnings("all")
@Repository
public class LogsDao extends BaseDao {

    public LogsDao() {
        this.iBaseEntity = new LogsEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, LogsEntity.class);
    }

    public void saveLogs(IBaseEntity entity) {
        JSONObject jsonObject = null;
        //获取ID
        String id = entity.getJSONObjectId().getString("ID");

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        JSONObject data = entity.getJSONColumns();
        Set<Map.Entry<String, Object>> set = data.entrySet();
        for (Iterator<Map.Entry<String, Object>> iterator = set.iterator(); iterator.hasNext(); ) {
            Map.Entry<String, Object> entry = iterator.next();
            sql.append(entry.getKey() + " AS " + entry.getValue() + ",");
        }
        sql.delete(sql.length() - 1, sql.length());
        sql.append(" FROM ");
        sql.append(entity.getTableName());
        sql.append(" WHERE 1 = 1 ");
        sql.append(" AND ID = ?");

        super.appendPageSql(sql, 1, Integer.MAX_VALUE);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), new Object[]{id});

        if (jsonList != null && jsonList.size() > 0) {
            jsonObject = jsonList.get(0);
        } else {
            jsonObject = null;
        }

        Set<Map.Entry<String, Object>> setColumns = entity.getJSONColumns().entrySet();
        for (Iterator<Map.Entry<String, Object>> iterator = setColumns.iterator(); iterator.hasNext(); ) {
            Map.Entry<String, Object> entry = iterator.next();
            String oldValue = null;
            if (null != jsonObject) {
                oldValue = jsonObject.getString(entry.getValue().toString());
            } else {
                return;
            }
            String newValue = entity.getJSONObject().getString(entry.getKey());
            StringBuffer sb = new StringBuffer();
            List<Object> sqlArgs = new ArrayList<Object>();
            sb.append("INSERT INTO T_SYS_LOGS (\n" +
                    "TABLE_SCHEMA,\n" +
                    "TABLE_NAME,\n" +
                    "COLUMN_NAME,\n" +
                    "COLUMN_NAME_FORMAT,\n" +
                    "OLD_VALUE,\n" +
                    "NEW_VALUE,\n" +
                    "UPDATE_USER_ID,\n" +
                    "UPDATE_TIME,\n" +
                    "UPDATE_ID\n" +
                    ")\n" +
                    "VALUES\n" +
                    "\t(?,?,?,?,?,?,?,?,?)\n");
            sqlArgs.add("oa");
            String updateUserId = entity.getJSONObjectUpdateUserId().getString("updateUserId");
            sqlArgs.add(StringUtils.isBlank(entity.getTableName()) ? null : entity.getTableName());
            sqlArgs.add(StringUtils.isBlank(entry.getKey()) ? null : entry.getKey());
            sqlArgs.add(StringUtils.isBlank(entry.getValue().toString()) ? null : entry.getValue().toString());
            sqlArgs.add(StringUtils.isBlank(oldValue) ? null : oldValue);
            sqlArgs.add(StringUtils.isBlank(newValue) ? null : newValue);
            sqlArgs.add(StringUtils.isBlank(updateUserId) ? null : updateUserId);
            sqlArgs.add(super.getCurrentTime());
            sqlArgs.add(id);
            if (StringUtils.isBlank(oldValue) & StringUtils.isBlank(newValue)) {

            } else {
                this.getJdbcTemplate().update(sb.toString(), sqlArgs.toArray());
            }
        }
        String deleteSql = "DELETE FROM T_SYS_LOGS WHERE TABLE_SCHEMA='oa' AND NEW_VALUE=OLD_VALUE";
        this.getJdbcTemplate().update(deleteSql);
    }

    public List<JSONObject> getColumnLogs(String tableName, String columnName, String updateId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_logs.COLUMN_NAME_FORMAT AS colnumName,\n" +
                "\tt_sys_logs.OLD_VALUE AS oldValue,\n" +
                "\tt_sys_logs.NEW_VALUE AS newValue,\n" +
                "\tt_sys_logs.UPDATE_USER_ID AS updateUserId,\n" +
                "\tt_sys_logs.UPDATE_TIME AS updateTime,\n" +
                "\tT_SYS_USER.NAME AS updateUserName\n" +
                "FROM\n" +
                "\tt_sys_logs\n" +
                "\tLEFT JOIN \n" +
                "\tT_SYS_USER ON T_SYS_USER.ID=t_sys_logs.UPDATE_USER_ID \n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_logs.TABLE_SCHEMA = 'oa'\n" +
                "AND t_sys_logs.TABLE_NAME = '" + tableName + "'\n" +
                "AND t_sys_logs.COLUMN_NAME_FORMAT = '" + columnName + "'\n" +
                "AND t_sys_logs.UPDATE_ID = '" + updateId + "'\n" +
                "ORDER BY t_sys_logs.UPDATE_TIME DESC\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getTableLogs(String tableName, String mainKey, String columnName, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_logs.oldValue,\n" +
                "\tt_logs.updateTime,\n" +
                "\tt_sys_user.`NAME` AS updateUserName\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t" + columnName + " AS oldValue,\n" +
                "\t\t\tUPDATE_USER_ID,\n" +
                "\t\t\tUPDATE_TIME AS updateTime\n" +
                "\t\tFROM\n" +
                "\t\t\t" + tableName + "\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND " + mainKey + " = '" + id + "'\n" +
                "\t) AS t_logs\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = t_logs.UPDATE_USER_ID\n" +
                "GROUP BY t_logs.oldValue\n" +
                "ORDER BY\n" +
                "\tt_logs.updateTime DESC ");
        return super.queryForJsonList(sql.toString());
    }


    public void addLogs(IBaseEntity entity) {
        JSONObject jsonObject = null;
        //获取ID
        String id = entity.getJSONObjectId().getString("ID");

        Set<Map.Entry<String, Object>> setColumns = entity.getJSONColumns().entrySet();
        for (Iterator<Map.Entry<String, Object>> iterator = setColumns.iterator(); iterator.hasNext(); ) {
            Map.Entry<String, Object> entry = iterator.next();
            String newValue = entity.getJSONObject().getString(entry.getKey());
            if (null != newValue && newValue.length() > 0) {
                StringBuffer sb = new StringBuffer();
                List<Object> sqlArgs = new ArrayList<Object>();
                sb.append("INSERT INTO T_SYS_LOGS (\n" +
                        "TABLE_SCHEMA,\n" +
                        "TABLE_NAME,\n" +
                        "COLUMN_NAME,\n" +
                        "COLUMN_NAME_FORMAT,\n" +
                        "OLD_VALUE,\n" +
                        "NEW_VALUE,\n" +
                        "UPDATE_USER_ID,\n" +
                        "UPDATE_TIME,\n" +
                        "UPDATE_ID\n" +
                        ")\n" +
                        "VALUES\n" +
                        "\t(?,?,?,?,?,?,?,?,?)\n");
                sqlArgs.add("oa");
                String updateUserId = entity.getJSONObjectUpdateUserId().getString("updateUserId");
                sqlArgs.add(StringUtils.isBlank(entity.getTableName()) ? null : entity.getTableName());
                sqlArgs.add(StringUtils.isBlank(entry.getKey()) ? null : entry.getKey());
                sqlArgs.add(StringUtils.isBlank(entry.getValue().toString()) ? null : entry.getValue().toString());
                sqlArgs.add(null);
                sqlArgs.add(StringUtils.isBlank(newValue) ? null : newValue);
                sqlArgs.add(StringUtils.isBlank(updateUserId) ? null : updateUserId);
                sqlArgs.add(super.getCurrentTime());
                sqlArgs.add(id);
                this.getJdbcTemplate().update(sb.toString(), sqlArgs.toArray());
            }
        }

    }

    public JSONObject getTableLogsNum(String tableName, String mainKey, String columnName, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(columTable.`column`) as num\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\tREPLACE(IFNULL(" + tableName + "." + columnName + ",''),'.00','') AS `column`\n" +
                "FROM\n" +
                "\t" + tableName + "\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND " + tableName + "." + mainKey + " = '" + id + " '\n" +
                "GROUP BY " + tableName + "." + columnName + "\n) as columTable\n");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sql.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return jsonObjectList.get(0);
        } else {
            return null;
        }
    }

    public JSONObject getColumnLogsNum(String tableName, String columnName, String updateId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tCOUNT(1) AS num\n" +
                "FROM\n" +
                "\tt_sys_logs\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND TABLE_NAME = '" + tableName + "'\n" +
                "AND (\n" +
                "\tCOLUMN_NAME = '" + columnName + "'\n" +
                "\tOR COLUMN_NAME_FORMAT = '" + columnName + "'\n" +
                ")\n" +
                "AND UPDATE_ID = '" + updateId + "'\n" +
                "AND REPLACE (\n" +
                "\tIFNULL(t_sys_logs.OLD_VALUE, ''),\n" +
                "\t'.00',\n" +
                "\t''\n" +
                ") <> REPLACE (\n" +
                "\tIFNULL(t_sys_logs.NEW_VALUE, ''),\n" +
                "\t'.00',\n" +
                "\t''\n" +
                ")\n");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sql.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return jsonObjectList.get(0);
        } else {
            return null;
        }
    }
}
